package com.swh.xb;

import com.alibaba.druid.pool.DruidDataSource;

import com.swh.entity.User;
import org.junit.Before;
import org.junit.Test;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;


import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class Demo01 {

    JdbcTemplate jdbcTemplate;

    @Before
    public void before()  {

        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUsername("root");

        Properties prop = new Properties();

//        prop.load(new FileInputStream("E:\\java46\\xb\\src\\main\\resources\\jdbc.properties"));

//        prop.load(this.getClass().getClassLoader().getResourceAsStream("jdbc.properties"));

        try {
            prop.load(Demo01.class.getClassLoader().getResourceAsStream("jdbc.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println(prop);

        String driverClassName = prop.getProperty("jdbc.driverClassName");
        String url = prop.getProperty("jdbc.url");
        String username = prop.getProperty("jdbc.username");
        String password = prop.getProperty("jdbc.password");

        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);

         jdbcTemplate = new JdbcTemplate(dataSource);

    }




    @Test
    public void test1() throws Exception {

        jdbcTemplate.execute("create table test(id int primary key auto_increment, name varchar(30));");

    }


    @Test
    public void test2() throws Exception {

        jdbcTemplate.execute("drop table test;");
    }


    @Test
    public void test3() throws Exception {

//        jdbcTemplate.update("insert into test values(null,'zhangs')");
        jdbcTemplate.update("insert into test values(null,?)", "lisi");
    }

    @Test
    public void test4() throws Exception {

        jdbcTemplate.update("update test set name=? where id=?", "newName", 1);
    }

    @Test
    public void test5() throws Exception {

        jdbcTemplate.update("delete from test where id=?", 1);
    }


    @Test
    public void test6() throws Exception {

        List<User> userList = jdbcTemplate.query("select * from user", new RowMapper<User>() {


            @Override
            public User mapRow(ResultSet rs, int index) throws SQLException {

                System.out.println("执行了" + index + "次");

                Long id = rs.getLong("id");
                String username = rs.getString("username");
                String password = rs.getString("password");

                User user = new User();

                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);

                return user;
            }
        });


        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void test7() throws Exception {

        List<User> userList = jdbcTemplate.query("select * from user", new ResultSetExtractor<List<User>>() {
            @Override
            public List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {

                System.out.println("执行了几次");
                List<User> users = new ArrayList<>();

                while (rs.next()) {
                    Long id = rs.getLong("id");
                    String username = rs.getString("username");
                    String password = rs.getString("password");

                    User user = new User();

                    user.setId(id);
                    user.setUsername(username);
                    user.setPassword(password);

                    users.add(user);
                }
                return users;
            }
        });

        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void test8() throws Exception {

        List<User> userList = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));

        for (User user : userList) {
            System.out.println(user);
        }

    }

    @Test
    public void test9() throws Exception {

        List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from user");

        for (Map<String, Object> map : maps) {

            System.out.println(map);

            Object username = map.get("username");
        }

    }

    @Test
    public void test10() throws Exception {

        // IncorrectResultSizeDataAccessException

        // EmptyResultDataAccessException
        Map<String, Object> map = jdbcTemplate.queryForMap("select * from user where id=?", 100);

        System.out.println(map);

    }

    @Test
    public void test11() throws Exception {

        // IncorrectResultSizeDataAccessException

        // EmptyResultDataAccessException
        User user = jdbcTemplate.queryForObject("select * from user where id=?", new BeanPropertyRowMapper<>(User.class), 100);

        System.out.println(user);
    }

    @Test
    public void test12() throws Exception {

        // IncorrectResultSizeDataAccessException

        // EmptyResultDataAccessException
        Long count = jdbcTemplate.queryForObject("select count(*) from user", Long.class);

        System.out.println(count);
    }


}
